package com.blank.web.dao;

import com.blank.web.bean.Student;
import com.blank.web.utils.DbUtil;
import com.blank.web.utils.TestIsEmpty;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImp implements StudentDao {

    @Override
    public List<Student> findAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取数据库的连接
        try {
            connection = DbUtil.getConnection();
            //sql语句
            String sql = "select * from t_students";
             preparedStatement = connection.prepareStatement(sql);
             resultSet = preparedStatement.executeQuery();
            List<Student> list = new ArrayList<Student>();
            while(resultSet.next()){

                Student stu = new Student();
                int sid = resultSet.getInt("sid");
                String sname = resultSet.getString("sname");
                String  sdate = resultSet.getString("sdate");
                String email = resultSet.getString("email");
                String gender = resultSet.getString("gender");
                String remark = resultSet.getString("remark");

                stu.setSid(sid);
                stu.setsName(sname);
                stu.setSdate(sdate);
                stu.setEmail(email);
                stu.setGender(gender);
                stu.setRemark(remark);
                list.add(stu);

            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if (null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public void AddStudent(Student student) {
        //获取数据库的连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
             connection = DbUtil.getConnection();
            //sql语句
            String sql = "insert into t_students values(null,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getsName());
            preparedStatement.setString(2,student.getGender());
            preparedStatement.setString(3,  student.getSdate());
            preparedStatement.setString(4,student.getEmail());
            preparedStatement.setString(5,student.getRemark());

            int i = preparedStatement.executeUpdate();
            if(i != 0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

    @Override
    public void DelStudent(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
         //获取连接
        try {
             connection = DbUtil.getConnection();
            //sql语句
            String sql = "delete from t_students where sid = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            int i = preparedStatement.executeUpdate();
            if(i > 0){
                System.out.println("删除成功!");

            }
        } catch (SQLException e) {

        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Student findStudentById(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DbUtil.getConnection();

            String sql = "select * from t_students where sid= ?";

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1,id);

            resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                Student student = new Student();
                int sid = resultSet.getInt("sid");
                String sname = resultSet.getString("sname");
                String  sdate = resultSet.getString("sdate");
                String email = resultSet.getString("email");
                String gender = resultSet.getString("gender");
                String remark = resultSet.getString("remark");

                student.setSid(sid);
                student.setsName(sname);
                student.setSdate(sdate);
                student.setEmail(email);
                student.setGender(gender);
                student.setRemark(remark);
                System.out.println(student);
                return student;
            }
            System.out.println("查找成功！");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if (null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public void UpdateStudent(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DbUtil.getConnection();

            String sql = "update t_students set sname = ?,gender = ?,sdate = ?,email=?,remark=? where sid = ?";

             preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,student.getsName());
            preparedStatement.setString(2,student.getGender());
            preparedStatement.setString(3,student.getSdate());
            preparedStatement.setString(4,student.getEmail());
            preparedStatement.setString(5,student.getRemark());
            preparedStatement.setInt(6,student.getSid());

            int i = preparedStatement.executeUpdate();
            if(i > 0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<Student> Search(String name) {
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        ResultSet resultSet = null;
        try {


             connection = DbUtil.getConnection();

            String sql = "select * from t_students where sname like ?";

            List<Student> list2 = new ArrayList<Student>();
            //判断是否有名字,若有则添加进sql语句


            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "%"+name+"%");
             resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Student stu = new Student();
                int sid = resultSet.getInt("sid");
                String sname = resultSet.getString("sname");
                String sdate = resultSet.getString("sdate");
                String email = resultSet.getString("email");
                String gender = resultSet.getString("gender");
                String remark = resultSet.getString("remark");

                stu.setSid(sid);
                stu.setsName(sname);
                stu.setSdate(sdate);
                stu.setEmail(email);
                stu.setGender(gender);
                stu.setRemark(remark);
                list2.add(stu);
            }
            System.out.println(list2);
            return list2;
        }catch  (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if (null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<Student> findStudentByPage(int currentPage) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
           connection = DbUtil.getConnection();

            String sql = "select * from t_students limit ? offset ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, PAGE_SIZE);
            preparedStatement.setInt(2,(currentPage-1)*PAGE_SIZE);
           resultSet = preparedStatement.executeQuery();
            List<Student> list3 = new ArrayList<Student>();
            while(resultSet.next()){
                Student stu = new Student();
                int sid = resultSet.getInt("sid");
                String sname = resultSet.getString("sname");
                String sdate = resultSet.getString("sdate");
                String email = resultSet.getString("email");
                String gender = resultSet.getString("gender");
                String remark = resultSet.getString("remark");

                stu.setSid(sid);
                stu.setsName(sname);
                stu.setSdate(sdate);
                stu.setEmail(email);
                stu.setGender(gender);
                stu.setRemark(remark);
                list3.add(stu);
            }
            return list3;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if (null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int findCount() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
             connection = DbUtil.getConnection();

            String sql = "select count(*) from t_students";

             preparedStatement = connection.prepareStatement(sql);

            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                int i = resultSet.getInt("count(*)");
                System.out.println(i);
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if (null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
}
